package com.andrew.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.jcp.xml.dsig.internal.MacOutputStream;

import com.andrew.dao.CartDao;
import com.andrew.entity.Book;
import com.andrew.entity.Cart;
import com.andrew.entity.CartDetail;
import com.andrew.entity.Customer;
import com.andrew.util.JDBCUtils;
import com.sun.org.apache.bcel.internal.generic.NEW;

public class CartDaoImpl implements CartDao {

	/***
	 * 添加购物车信息
	 * param：bookId：书本编号， quantity：数量，	Cart购物车对象
	 * return：修改行数
	 */
	@Override
	public int addCartDetailInfo(int bookId,int quantity,Cart cart)throws SQLException {
		
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		String sql="INSERT INTO CartDetail VALUES(0,?,?,?)";
		return qr.update(sql,bookId,quantity,cart.getCartId());
		
	}
	/***
	 * 查询购物车
	 * param：CustomerId：顾客编号
	 * return Cart
	 */
	@Override
	public Cart queryCart(int customerId) throws SQLException {
		// TODO Auto-generated method stub
		//获取连接池
		DataSource dataSource = JDBCUtils.getDataSource();
				
		//新建一个执行对象
		QueryRunner qr=new QueryRunner(dataSource);
				
		//执行sql语句并封装到JavaBean对象中
		String sql="SELECT * FROM Cart WHERE CustomerId=?";
		return qr.query(sql, new BeanHandler<Cart>(Cart.class),customerId);
	}

	@Override
	public CartDetail queryCartDetailByBookIdAndCartId(int bookId, Cart cart) throws SQLException {
		QueryRunner qRunner=new QueryRunner(JDBCUtils.getDataSource());
		// TODO Auto-generated method stub
		String sql="select * from CartDetail where BookId=? AND CartId=? ";
		return qRunner.query(sql,new BeanHandler<CartDetail>(CartDetail.class),bookId,cart.getCartId());
		
		
	}
	/***
	 * 修改购物车数量
	 * 每次加一
	 */

	@Override
	public int udateQuantity(int service,int bookId, Cart cart)throws SQLException {
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		
		String sqlAdd="UPDATE CartDetail set quantity=quantity+1 where CartId=? and BookId=?";//购物车加一
		String sqlMinus="UPDATE CartDetail SET quantity=quantity-1 where CartId=? and BookId=?";
		
		if(service==1) {//购物车加
			return qr.update(sqlAdd,cart.getCartId(),bookId);
		}else {
			return qr.update(sqlMinus,cart.getCartId(),bookId);
		}
		
		
	}

	@Override
	public int getTotalCountOfCart(Cart cart) throws SQLException {
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		String sql="SELECT COUNT(*) FROM CartDetail where CartId=?";
		return qr.query(sql, new ScalarHandler<Integer>(),cart.getCartId());
	}

	@Override
	public List<CartDetail> queryCartDetail(Cart cart) throws SQLException {
		
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		String sql="SELECT * FROM CartDetail where CartId=?";	
		return qr.query(sql, new BeanListHandler<CartDetail>(CartDetail.class),cart.getCartId());

		
	}

	@Override
	public void deleteByCustomerId(Customer customer) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Cart cart = this.queryCart(customer.getCustomerId());
		String sql="delete from CartDetail where CartId=?";
		qr.update(sql,cart.getCartId());
	}

	@Override
	public long countCartDetailsByCart(Cart cart) throws SQLException {
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		String sql="select count(*) from CartDetail where CartId=?";
		return qr.query(sql, new ScalarHandler<Long>(),cart.getCartId());
	}
//	@Override
//	public CartDetail queryAlreadyBoughtExist(Cart cart, int bookId) throws SQLException {
//		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
//		String sql="select * from CartDetail where CartId=? and BookId=?";
//		return qr.query(sql, new BeanHandler<CartDetail>(CartDetail.class),cart.getCartId(),bookId);
//	}
	@Override
	public int deleteOneCartDetail(int bookId, Cart cart) throws SQLException {
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		String sql="delete from CartDetail where BookId=? and CartId=?";
		return qr.update(sql,bookId,cart.getCartId());
	}
	@Override
	public String queryCartDetailQuantity(int bookId, Cart cart) throws SQLException {
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		String sql="SELECT  Price,quantity FROM CartDetail c , Book b WHERE c.CartId=? AND b.BookId=?  AND c.BookId=b.BookId";
		Map<String, Object> map = qr.query(sql,new MapHandler(),cart.getCartId(),bookId);
		
		BigDecimal quantity=new BigDecimal(map.get("quantity").toString());
		BigDecimal price=new BigDecimal(map.get("Price").toString());
		
		String result=quantity+","+price.multiply(quantity);
		return result;
	}



	
	


}
